package com.zhufei.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.zhufei.entity.News;
import com.zhufei.entity.UserInfo;

public class ZhuFeiUserInfoDao extends DBCon{
	private static String TABLE = "zhufei_user_info";
	private static String ID = "ID";
	private static String USERNAME = "UserName";
	private static String PASSWORD = "password";
	private static String REAL_NAME = "real_name";
	private static String SEX = "sex";
	private static String QQ = "qq";
	private static String IDENTIFICATION = "identification";
	private static String TELEPHONE = "telephone";
	private static String AVATAR_ROUTE = "avatar_route";
	private static String QUESTION = "question";
	private static String ANSWER = "answer";
	private static String MAIL = "mail";
	private static String MOTTO = "motto";
	private static String PROFILE = "profile";
	/**
	 * 修改zhufei_user_info表中数据
	 * @param ID  ID
	 * @param username 用户名，这个是论坛表bbscs_userdetail中的外键，此表不可修改
	 * @param mail 邮箱
	 * @param real_name 真实名字
	 * @param sex  性别，“男”或者“女”
	 * @param qq  qq号码
	 * @param identification 身份证号码
	 * @param telephone 电话号码
	 * @param avatar_route 头像图片的路径
	 * @param motto 座右铭
	 * @param profile 个人简介
	 * @param question 问题
	 * @param answer 答案
	 * @return  true表示成功，false表示失败
	 */
	public static boolean updateZhuFeiUserInfo(String id, String username, String mail, String real_name, String sex, String qq, String identification, String telephone, String avatar_route, String motto, String profile, String question, String answer){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "update "+TABLE+" set "+MAIL+"='"+mail+"',"+REAL_NAME+"='"+real_name+"',"+SEX+"='"+sex+"',"+QQ+"='"+qq+"',"+IDENTIFICATION+"='"+identification+"',"+TELEPHONE+"='"+telephone+"',"+AVATAR_ROUTE+"='"+avatar_route+"',"+MOTTO+"='"+motto+"',"+PROFILE+"='"+profile+"',"+QUESTION+"='"+question+"',"+ANSWER+"='"+answer+"'"+
				" where "+ID+"='"+id+"'";
			System.out.println(sql);
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 登录验证函数
	 * @param username 用户名
	 * @param password 密码
	 * @return true或者false返回用户名和密码正确与否
	 */
	public static boolean login(String username, String password){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select "+PASSWORD+" from "+TABLE+" where "+USERNAME+"='" +username +"'";
			ResultSet rs = statement.executeQuery(sql);
			if(!rs.next())
				return false;
			if(password.equals(rs.getString(PASSWORD)))
				return true;
			else
				return false;
					
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 删除操作
	 * @param ID   ID
	 * @return  true表示成功，false表示失败
	 */
	public static boolean deleteZhuFeiUserInfo(String id){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "delete from "+TABLE+" where "+ID+"='"+id+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 插入一条记录，在注册的时候调用
	 * @param username  用户名
	 * @param password 密码
	 * @param mail 邮件
	 * @param sex 性别，“男”或者“女”
	 * @param question  问题
	 * @param answer    答案
	 * @return  返回true表示成功插入，false表示失败
	 */
	public static boolean insertZhuFeiUserInfo(String username, String password, String mail, String sex, String question, String answer){
		DBCon con = new DBCon();
		try {
			String id = GlobalsDao.getUUID();
			Statement statement = con.getStatement();
			String sql = "insert into "+TABLE+"("+ID+","+USERNAME+","+PASSWORD+","+MAIL+","+SEX+","+QUESTION+","+ANSWER+") " + 
				"values ('"+id+"','"+username+"','"+password+"','"+mail+"','"+sex+"','"+question+"','"+answer+"')";
			//System.out.println(sql);
			statement.execute(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 根据用户名获取用户信息
	 * @param username 用户名
	 * @return 返回用户信息对象
	 */
	public static UserInfo getZhuFeiUserInfo(String username){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select * from "+TABLE+" where "+USERNAME+"='"+username+"'";
			ResultSet rs = statement.executeQuery(sql);
			UserInfo userInfo = new UserInfo();
			if(rs.next()){
				userInfo.setID(rs.getString(ID));
				userInfo.setPassword(rs.getString(PASSWORD));
				userInfo.setUsername(rs.getString(USERNAME));
				userInfo.setMail(rs.getString(MAIL));
				userInfo.setReal_name(rs.getString(REAL_NAME));
				userInfo.setSex(rs.getString(SEX));
				userInfo.setQq(rs.getString(QQ));
				userInfo.setIdentification(rs.getString(IDENTIFICATION));
				userInfo.setTelephone(rs.getString(TELEPHONE));
				userInfo.setAvatar_route(rs.getString(AVATAR_ROUTE));
				userInfo.setMotto(rs.getString(MOTTO));
				userInfo.setProfile(rs.getString(PROFILE));
				userInfo.setQuestion(rs.getString(QUESTION));
				userInfo.setAnswer(rs.getString(ANSWER));
				rs.close();
				return userInfo;
			}else{
				rs.close();
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
	
	/**
	 * 获取数据库中会员数量总数
	 * @return  返回数据库有多少会员
	 */
	public static int getCountOfZhuFeiUserInfo(){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE;
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				rs.close();
				return count;
			}
			else {
				rs.close();
				return 0;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的用户信息对象
	 * @param index  从第index条开始获取（第一条用户信息下标是0）
	 * @param number  获取用户信息的数量，输入-1则获取数据库中所有用户信息
	 * @param type   返回的用户对象类型（3表示仅仅返回已填扩展信息，2表示仅仅返回未填扩展信息，1表示不管是否填写扩展信息都返回）
	 * @return  用户信息对象的ArrayList
	 */
	public static ArrayList<UserInfo> getZhuFeiUserInfo(int index, int number, int type){
		ArrayList<UserInfo> userInfoList = new ArrayList<UserInfo>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			String where = "";
			if(type==3){
				where = " where "+REAL_NAME+"!='0' ";
			}else if(type==2){
				where = " where "+REAL_NAME+"='0' ";
			}
			if(number != -1){
				sql = "select * from "+ TABLE +where+" limit "+index+","+number;
			}else{
				sql = "select * from "+ TABLE + where;
			}
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				UserInfo userInfo = new UserInfo();
				userInfo.setID(rs.getString(ID));
				userInfo.setPassword(rs.getString(PASSWORD));
				userInfo.setUsername(rs.getString(USERNAME));
				userInfo.setMail(rs.getString(MAIL));
				userInfo.setReal_name(rs.getString(REAL_NAME));
				userInfo.setSex(rs.getString(SEX));
				userInfo.setQq(rs.getString(QQ));
				userInfo.setIdentification(rs.getString(IDENTIFICATION));
				userInfo.setTelephone(rs.getString(TELEPHONE));
				userInfo.setAvatar_route(rs.getString(AVATAR_ROUTE));
				userInfo.setMotto(rs.getString(MOTTO));
				userInfo.setProfile(rs.getString(PROFILE));
				userInfo.setQuestion(rs.getString(QUESTION));
				userInfo.setAnswer(rs.getString(ANSWER));
				userInfoList.add(userInfo);
			}
			rs.close();
			return userInfoList;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
	
	/**
	 * 重新设置密码
	 * @param username 用户名
	 * @param password 新密码
	 * @return 返回true表示成功，false表示失败
	 */
	public static boolean resetPassword(String username, String password){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "update "+TABLE+" set "+PASSWORD+"='"+password+"' where "+USERNAME+"='"+username+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
}
